package com.cn.hotel.dao.Impl;

import com.cn.hotel.dao.IUserDao;
import com.cn.hotel.model.PageModel;
import com.cn.hotel.model.UserModel;
import com.cn.hotel.util.JDBC;
import com.mysql.cj.protocol.Resultset;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserImpl implements IUserDao {
    /**
     * 页面注册信息输入，用户新增公用
     */
    @Override
    public int add(UserModel userModel) {
        //1、封装sql语句
        String sql = "insert into user(username,password,phone,sex,hobby,birthday,introduce) values('" + userModel.getUsername() + "'" +
                ",'" + userModel.getPassWord() + "','" + userModel.getPhone() + "','" + userModel.getSex() + "','" + userModel.getHobby() + "'," +
                "'" + userModel.getBirthday() + "','" + userModel.getIntroduce() + "')";
        /*2、执行sql语句
          JDBC.executeUpdate(sql);
         */
        //3、返回数据结果
        return JDBC.executeUpdate(sql);
    }

    /**
     * 注册判断
     */
    @Override
    public UserModel findByUsername(String userName) {
        String sql = "select * from user where username='" + userName + "'";
        UserModel userModel = null;
        ResultSet resultSet = JDBC.getResultSet(sql);
        try {
            while (resultSet.next()) {
                userModel = new UserModel();
                userModel.setUsername(resultSet.getString("userName"));
            }
        } catch (Exception e) {
            System.out.println("数据查询错误");
        }
        return userModel;
    }

    /**
     * 登录判断，
     */
    @Override
    public UserModel isHaveFindByUsernamePassword(UserModel userModel) {
        String sql = "select*from user where username='" + userModel.getUsername() + "'and password='" + userModel.getPassWord() + "'";
        UserModel model = null;
        ResultSet resultSet = JDBC.getResultSet(sql);
        try {
            while (resultSet.next()) {
                model = new UserModel();//只需知道查询出来是否为空就可
            }
        } catch (Exception e) {
            System.out.println("数据查询错误");
        }
        return model;
    }

    /**
     * 获得用户数据集合 --- 用户查询
     */
//    @Override
//    public List<UserModel> findAll(PageModel pageModel) {
//        String sql = "select*from user  limit  "+(pageModel.getPageNum()-1)*pageModel.getPageSize()+","+pageModel.getPageSize()+"";
//        List<UserModel> list = new ArrayList<>();
//        ResultSet resultSet = JDBC.getResultSet(sql);
//        try {
//            while (resultSet.next()) {
//                UserModel userModel = new UserModel();
//                userModel.setId(resultSet.getInt("id"));
//                userModel.setUsername(resultSet.getString("username"));
//                userModel.setPhone(resultSet.getString("phone"));
//                userModel.setSex(resultSet.getString("sex"));
//                userModel.setHobby(resultSet.getString("hobby"));
//                userModel.setBirthday(resultSet.getString("birthday"));
//                userModel.setIntroduce(resultSet.getString("introduce"));
//                list.add(userModel);
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        return list;
//    }

    /**
     * 实现用户数据删除操作
     */
    @Override
    public int deleteById(Integer id) {
        String sql = "delete from user where id = " + id;
        int count = JDBC.executeUpdate(sql);
        return count;
    }
    /**
     * 用户模糊查询操作
     *
     * @param userModel
     * @return
     */
    @Override
    public List<UserModel> findAll(UserModel userModel) {
        String sql = "select * from user where 1=1";
        if (!"".equals(userModel.getUsername())) {
            sql += " and username like '%" + userModel.getUsername() + "%'";
        }
        if (!"".equals(userModel.getPhone())) {
            sql += " and phone like '" + userModel.getPhone() + "'";
        }
        if (!"".equals(userModel.getSex())&&!"全部".equals(userModel.getSex())) {
            sql += " and sex = '" + userModel.getSex() + "'";
        }
        if (!"".equals(userModel.getHobby())) {
            sql += " and hobby= '" + userModel.getHobby() + "'";
        }
        if (!"".equals(userModel.getStartBirthday()) && "".equals(userModel.getEndBirthday())) {
            sql += " and DATE_FORMAT(birthday,'%Y-%m-%d') > " + userModel.getStartBirthday();
        }
        if ("".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql += " and DATE_FORMAT(birthday,'%Y-%m-%d') < " + userModel.getEndBirthday();
        }
        if (!"".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql += " and DATE_FORMAT(birthday,'%Y-%m-%d') between '" + userModel.getStartBirthday() + "' and '" + userModel.getEndBirthday() + "'";
        }
        if (!"".equals(userModel.getIntroduce())) {
            sql += " and introduce like '" + userModel.getIntroduce() + "'";
        }
         sql += " order by id asc" + " limit  "+(userModel.getPageModel().getPageNum()-1)*userModel.getPageModel().getPageSize()+"," +
                 ""+userModel.getPageModel().getPageSize()+"";

        List<UserModel> list = new ArrayList<>();
        ResultSet resultSet = JDBC.getResultSet(sql);
        try {
            while (resultSet.next()) {
                UserModel userModel1 = new UserModel();
                userModel1.setId(resultSet.getInt("id"));
                userModel1.setUsername(resultSet.getString("username"));
                userModel1.setPhone(resultSet.getString("phone"));
                userModel1.setSex(resultSet.getString("sex"));
                userModel1.setHobby(resultSet.getString("hobby"));
                userModel1.setBirthday(resultSet.getString("birthday"));
                userModel1.setIntroduce(resultSet.getString("introduce"));
                list.add(userModel1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBC.close();
        }
        return list;
    }


    /**
     * 用户编辑，点击后进行查询该id的数据，然后把数据返回到页面响应。
     * 查完id 需要将此id对应的所有的值返回给页面展示，所以返回值类型为userModel
     *
     * @return
     */
    @Override
    public UserModel findById(Integer id) {
        String sql = "select * from user where id=" + id;
        ResultSet resultSet = JDBC.getResultSet(sql);
        UserModel userModel = new UserModel();
        try {
            while (resultSet.next()) {
                userModel.setId(resultSet.getInt("id"));
                userModel.setUsername(resultSet.getString("username"));
                userModel.setPhone(resultSet.getString("phone"));
                userModel.setSex(resultSet.getString("sex"));
                userModel.setHobby(resultSet.getString("hobby"));
                userModel.setBirthday(resultSet.getString("birthday"));
                userModel.setIntroduce(resultSet.getString("introduce"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userModel;
    }

    /**
     * 用户编辑的sql语句编写
     *
     * @param userModel
     * @return
     */
    @Override
    public int edit(UserModel userModel) {
        StringBuilder sql = new StringBuilder("update user set");
        if (userModel.getUsername()!="") {
           sql.append(" username='"+userModel.getUsername()+"',");
        }
        if(userModel.getPassWord()!=""){
            sql.append(" password='"+userModel.getPassWord()+"',");
        }
        sql.append(" phone='"+userModel.getPhone()+"',");
        sql.append(" sex='"+userModel.getSex()+"',");
        sql.append(" birthday='"+userModel.getBirthday()+"',");
        sql.append(" hobby='"+userModel.getHobby()+"',");
        sql.append(" introduce='"+userModel.getIntroduce()+"',");
        sql.append(" remark='' where id="+userModel.getId());
        return JDBC.executeUpdate(sql.toString());
    }

    /**
     * 获得数据库的总条数
     * @param userModel
     * @return
     */
    @Override
    public int getCount(UserModel userModel) {
        String sql = "select count(*) count from user where 1=1";
        if (!"".equals(userModel.getUsername())) {
            sql += " and username like '%" + userModel.getUsername() + "%'";
        }
        if (!"".equals(userModel.getPhone())) {
            sql += " and phone like '" + userModel.getPhone() + "'";
        }
        if (!"".equals(userModel.getSex())&&!"全部".equals(userModel.getSex())) {
            sql += " and sex = '" + userModel.getSex() + "'";
        }
        if (!"".equals(userModel.getHobby())) {
            sql += " and hobby= '" + userModel.getHobby() + "'";
        }
        if (!"".equals(userModel.getStartBirthday()) && "".equals(userModel.getEndBirthday())) {
            sql += " and DATE_FORMAT(birthday,'%Y-%m-%d') > " + userModel.getStartBirthday();
        }
        if ("".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql += " and DATE_FORMAT(birthday,'%Y-%m-%d') < " + userModel.getEndBirthday();
        }
        if (!"".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql += " and DATE_FORMAT(birthday,'%Y-%m-%d') between '" + userModel.getStartBirthday() + "' and '" + userModel.getEndBirthday() + "'";
        }
        if (!"".equals(userModel.getIntroduce())) {
            sql += " and introduce like '" + userModel.getIntroduce() + "'";
        }
        ResultSet resultSet = JDBC.getResultSet(sql);
        /**
         * 将返回的结果集转换为int
         */
        int count = 0;
        try {
            resultSet.next();
             count = resultSet.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }


}
